<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><head><title>CS 234: Programming in Visual Basic</title></head><body>

<center><h4>
            CS334 - Prog5: Database Application
            <br>
            (20 Points)
</h4></center>
      Create a VB program to access multiple tables of a database. 
      The solution has two projects: FormGrid and UserProg. 
      
      
<h4>The Database Table</h4>
The database file is Company.mdb. 
The database has five tables, and the user program will access all five tables.
The table schemas are as follows:
      <pre>Table Employees      
    EmployeeID  : Integer, required
    Name        : Varchar(50), required
    Email       : Varchar(30)
    Extension   : Varchar(4)
    Address     : VarChar(255)
    
    Field EmployeeID is the primary key.
    
Table Customers      
    AccountNo   : Integer, required
    Name        : Varchar(50), required
    Street      : VarChar(50), required
    City        : VarChar(50), required
    State       : VarChar(2), required
    Street      : VarChar(50), required
    Phone       : Varchar(15), required
    Fax         : Varchar(15)
    Email       : Varchar(30)
    
    Field AccountNo is the primary key.
    
Table Products
    ProductID           : Char(4), required
    ProductName         : Varchar(50), required
    ProductDescription  : Varchar(255)
    UnitPrice           : Currency
    
    Field ProductID is the primary key.
    
Table Orders
    OrderID         : Integer, required
    CustomerID      : Integer,required, References Customers(AccountNo)
    OrderDate       : Date, required
    ShipDate        : Date
    SalesTaxRate    : Double, required, default 0.05, less than one
    EmployeeID      : Integer, required, References Employee
    
    Field OrderID is the primary key.
    
Table OrderDetails
    OrderID         : Integer, required, References Orders
    ProductID       : Char(4), required, References Products
    Quantity        : Integer, required
    LineTotal       : Double, required, default 0.05, less than one
    
    Fields OrderID and ProductID are the primary key.
</pre>

<h4> Loading Database Table </h4>
<ol>
<li> You are <b>NOT ALLOWED</b> to setup connection and adapter at design time. You <b>MUST</b> do it in code. </li>
<li> Your program must store the location and the name of the database file. </li>
<li> If the file location or file name changes, your program must use
an Open File Dialog for the user to select the new database file. </li>
<li> If the selected database file does not work, your program must display the Open File Dialog again. </li>
<li> If the user does not select a database file, your program should display a message box and terminate. </li>
<li> If the selected database file works for the program, the new file name/location must be stored for future use. </li>
</ol>

<h4>Project FormGrid </h4>
      <ol>
         <li>
         The project has one form, which has one BindingSource, one BindingNavigator and one DataGridView. </li>
         <li>
         The BindingNavigator has three additional buttons with text "UPDATE," "RELOAD" and "BACK." </li>
         <li>
         The project is a class library project, the name of the form class is FormGrid, 
         the assembly name is FormGrid.dll, and the root namespace is "UWPCS3340." </li>
         <li> The form class can have other fields, properties, and methods, but it is <b>NOT ALLOWED</b> 
         to load records from any databases. </li>
      </ol>

<h4> Project UserProg </h4>
      <ol>
         <li>
         The project <b>MUST</b> have a module with name DataModule.vb that creates objects and 
         provides method(s) to load records from all tables of the database. </li>
         <li>
         The project <b>MUST</b> create three objects of class FormGrid to display the records 
         of tables Employees, Customers and Products.         </li>
         <li> 
         Your forms <b>MUST</b> be similar to the forms of the sample program, and your controls <b>MUST</b>
         do the work in the same as that in the sample program.  </li>
         <li> <b>Five Bonus Points</b>: Run the sample program and find out.          </li>
      </ol>

<h4> Form Orders and Order Details </h4>
<ol>
    <li> The form has two datagridviews, one to display records of table Orders, another table Details. </li>
    <li> You <b>MUST</b> bind the two datagridview to two dataviews. </li>
    <li> All records in the details datagridview are the details records for the current order record 
        in the order datagridview. When a different record is selected in the order datagridview, 
        the records in the details datagridview will be changed accordingly. </li>
    <li> Each datagridview has a button with text "SAVE" below its bottom-left corner.
        Clicking on the button will save all updates to the corresponding datagridview to the database;
        a message box will be displayed when any integrity rule in the database is violated, the
        updates will be rejected, and the datagridview will show the records before updating. </li>
    <li> The form has three radio buttons with text "All Orders," "Orders from Customer" and 
        "Orders by Employee." </li>
    <li> There are two textboxes below the radio buttons, one to display ID and
        the other to display name. </li>
    <li> There are three buttons below the textboxes: the first one has text "&lt;" for previous record,
         the second one has text "&gt;" for next record, and the last one has text "BACK" to go back
         to the main form. </li>
    <li> When clicking the All Orders radio button
        <ul>
         <li> the two textboxes will be cleared </li>
         <li> the two previous/next button will be disabled </li> 
         <li> all order records are displayed in the order datagridview with the first record selected.</li>
         </ul>
      </li>
    <li> When clicking the Order from Customer radio button
        <ul>
        <li> the two textboxes will display the ID and name of the first record in table Customers</li>
        <li> the previous button will be disabled</li>
        <li> the next nutton will be enabled if the Customers table has more than one record </li>
        <li> clicking on the next (previous) button will display the ID and name of the next (previous) 
            customer in the two textboxes </li>
        <li> all order records from the selected (displayed) customer are displayed in the
         order datagridview.</li>
         </ul>
    </li>
    <li> When clicking the Order by Employee radio button
        <ul>
        <li> the two textboxes will display the ID and name of the first record in table Employees </li>
        <li> the previous button will be disabled</li>
        <li> the next nutton will be enabled if the Employees table has more than one record </li>
        <li> clicking on the next (previous) button will display the ID and name of the next (previous) 
            employee in the two textboxes </li>
        <li> all order records from the selected (displayed) employee are displayed in the
         order datagridview.</li>
         </ul>
    </li>
</ol>

<h4>Submission </h4>
<ol>
<li> You must do the program in pairs, and you must have a different partner from previous programs. </li>
<li> You must sign a pairing sheet by 11 AM, Thursday, March 29. 
     <b>Otherwise, you will lose two points.</b> </li>
<li> Use your UserName to name the solution folder. </li>
<li> Drop your solution folder to folder Prog5 of the class DROP folder by 11 PM, Tuesday, April 3, 2007. </li>
<li> You must send an email to the instructor by 11 PM, Tuesday, April 3, 2007.
     <br> 
     Answer the following three questions in the email (1 is the lowest score and 5 is the highest score):
     <ol>
     <li> What do you think of your partner's programming skill? (1 to 5) </li>
     <li> What do you think of your partner's contribution to the program? (1 to 5) </li>
     <li> Do you want to work with your partner in the future? (1 to 5) </li>
     </ol>
     </li>
<li> You may lose up to two points for incorrect submission, including missing email and incorrect folder name. </li>
<li> You must follow the programming rules, and you may lose up to 2 points on style. </li>
</ol>

</body></html>